<%@page import="java.text.DecimalFormatSymbols"%>
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
	pageEncoding="ISO-8859-1"%>
<%@ page language="java" import="java.sql.*"%>

<%!public String getQuarter(int i) {
		String quarter;
		switch (i) {
		case 1:
			quarter = "Fall";
			break;

		case 2:
			quarter = "Winter";
			break;

		case 3:
			quarter = "Spring";
			break;

		case 4:
			quarter = "Summer";
			break;
		default:
			quarter = "ERROR";
		}

		return quarter;
	}%>
<%
	Class.forName("org.postgresql.Driver");
	Connection conn = DriverManager.getConnection(
			"jdbc:postgresql://localhost:5432/cse132b", "cse132blogin",
			"1234567890");
	Statement stmt;
	int ID;

	ID = Integer.valueOf(request.getParameter("ID"));

	stmt = conn.createStatement();
	ResultSet rs = stmt
			.executeQuery("SELECT * FROM (SELECT students.*, classes.*, courses.*, enroll.* FROM students , enroll, classes, courses WHERE students.student_id = enroll.student_id AND classes.class_id = enroll.section_id AND courses.course_id = classes.course_id AND enroll.student_id="
					+ String.valueOf(ID)
					+ " ) AS AAA  LEFT JOIN GRADE_CONVERSION ON AAA.grade_letter = GRADE_CONVERSION.letter_grade ORDER BY year, quarter");

	String HTML = "";
	String myFirstName = "", myMiddleName = "", myLastName = "";
	String myQuarter = "";
	boolean isFoot = false;
	boolean quit = false;
	int row = 0;
	double GPA = 0, myGrade=0;
	int TotalUnits = 0;
	while (rs.next()) {

		myFirstName = rs.getString("first_name");
		myMiddleName = rs.getString("middle_name");
		myLastName = rs.getString("last_name");

		if (!myQuarter.equals(rs.getString("year")
				+ rs.getString("quarter"))) {
			if (row > 0) {
				GPA = myGrade / TotalUnits;
				HTML += "<tr>";
				HTML += "	<td colspan=9> GPA: " +  String.format("%4.2f", GPA) + "</td>";
				HTML += "</tr>";
				myGrade = 0;
				TotalUnits = 0;
			}
			myQuarter = rs.getString("year") + rs.getString("quarter");
			HTML += "<tr>";
			HTML += "	<td colspan=9>Year: "
					+ rs.getString("year")
					+ ", Quarter: "
					+ getQuarter(Integer.valueOf(rs
							.getString("quarter"))) + "</td>";
			HTML += "</tr>";
			//isFoot = true;
		}
		String s;
		s = rs.getString("grade_letter");
		if (s==null) s="";

		HTML += "<tr>";
		HTML += "	<td></td>";
		HTML += "	<td>" + rs.getString("Section_id") + "</td>";
		HTML += "	<td>" + rs.getString("course_number") + "</td>";
		HTML += "	<td>" + rs.getString("title") + "</td>";
		HTML += "	<td>" + rs.getString("enrollment_limit") + "</td>";
		HTML += "	<td>" + rs.getString("units") + "</td>";
		if (rs.getBoolean("requires_lab"))
			HTML += "	<td>Yes</td>";
		else
			HTML += "	<td>No</td>";
		HTML += "	<td>" + s + "</td>";
		HTML += "</tr>";
		
		if (!s.equals("IN"))
		{
			TotalUnits += rs.getInt("units");
		}
		myGrade += (rs.getFloat("number_grade") * rs.getInt("units"));

		row++;

	}

	GPA = myGrade / TotalUnits;
	HTML += "<tr>";
	HTML += "	<td colspan=9> GPA: " +  String.format("%4.2f", GPA) + "</td>";
	HTML += "</tr>";

	rs.close();
	stmt.close();
%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Insert title here</title>
</head>
<body>
	<table border="1">
		<tr>
			<td valign="top" width="30%">
				<%-- -------- Include menu HTML code -------- --%> <jsp:include
					page="/menu.html" />
			</td>
			<td>Display the classes currently taken by student <br> <br>
				Full name: <%
 	out.print((myFirstName + " " + myMiddleName + " " + myLastName));
 %> <br> <br>
				<table border="1">
					<tr>
						<th>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</th>
						<th>Section ID</th>
						<th>Course Name</th>
						<th>Title</th>
						<th>Enroll Limit</th>
						<th>Units</th>
						<th>Lab</th>
						<th>Grade</th>
					</tr>
					<%=HTML%>
				</table>

			</td>
		</tr>
	</table>
</body>
<%
	// Close the Connection
	conn.close();
%>